/**
 * 
 */
package qct;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import qct.pojo.Conference;
import qct.util.CustomerContextHolder;
import qct.util.DateUtil;
import qct.vo.MainRow;

/**
 * @author chentao.qu
 * @since 2012-8-8
 * 
 */
@Repository
public class MainDAO {
	Logger logger = Logger.getLogger(MainDAO.class);
	
	private JdbcTemplate jdbcTemplate;
	
	private DataSource dynamicDataSource;
	
	private List<Conference> mmpDayConfs;
	private List<Conference> tcDayConfs;
	private List<Conference> yunDayConfs;

	/**
	 * @param mmpDayConfs2
	 * @param hour
	 * @return
	 */
	private List<Conference> findMMPConferenceByHour(
			List<Conference> dayConfs, int hour) {
		List<Conference> hourConferences = new ArrayList<Conference>();
		if(mmpDayConfs != null) {
			Calendar cal = Calendar.getInstance();
			for(Conference c: mmpDayConfs) {
				cal.setTime(DateUtil.String2Date(c.getBeginTime(), "yyyyMMddHHmm"));
				if(hour == cal.get(Calendar.HOUR_OF_DAY)) {
					c.setTime(hour);
					hourConferences.add(c);
				}
			}
		}
		return hourConferences;
	}

	/**
	 * @param dayConfs
	 * @param hour
	 * @return
	 */
	private List<Conference> findTCConferenceByHour(
			List<Conference> dayConfs, int hour) {
		List<Conference> hourConferences = new ArrayList<Conference>();
		if(tcDayConfs != null) {
			Calendar cal = Calendar.getInstance();
			for(Conference c: tcDayConfs) {
				cal.setTime(DateUtil.String2Date(c.getBeginTime(), "yyyy-MM-dd HH:mm:ss"));
				if(hour == cal.get(Calendar.HOUR_OF_DAY)) {
					c.setTime(hour);
					c.setCustomName(queryCustomName(c.getCustomCode()));
					hourConferences.add(c);
				}
			}
		}
		return hourConferences;
	}

	/**
	 * @param dayConfs
	 * @param hour
	 * @return
	 */
	private List<Conference> findYunConferenceByHour(
			List<Conference> dayConfs, int hour) {
		List<Conference> hourConferences = new ArrayList<Conference>();
		if(yunDayConfs != null) {
			Calendar cal = Calendar.getInstance();
			for(Conference c : yunDayConfs) {
				cal.setTime(DateUtil.String2Date(c.getBeginTime(), "yyyy-MM-dd HH:mm"));
				if(hour == cal.get(Calendar.HOUR_OF_DAY)) {
					c.setTime(hour);
					c.setCustomName(queryCustomName(c.getCustomCode()));
					hourConferences.add(c);
				}
			}
		}
		return hourConferences;
	}

	/**
	 * @param startdate
	 * @return
	 */
	public List<MainRow> queryAllConference(String startdate) {
		List<MainRow> mainRowList = new ArrayList<MainRow>();
		for(int i=0; i<24; i++) {
			MainRow mr = new MainRow();
			mr.setTime(i);
			mr.setMmpConference(queryMMPConference(startdate, i));
			mr.setTcConference(queryTCConference(startdate, i));
			mr.setYunConference(queryYunConference(startdate, i));
			
			mainRowList.add(mr);
		}
		
		return mainRowList;
	}


	public String queryCustomName(String customCode) {
		setDataSource(CustomerContextHolder.MSSQLDATASOURCE);
		String sql = "select customername from gnetmis.dbo.bo_cu_customer where customercode ='"
				+ customCode + "'";
		return this.jdbcTemplate.queryForObject(sql, String.class);
	}

	/**
	 * @param startdate
	 * @param hour 
	 * @return
	 */
	private List<Conference> queryMMPConference(String startdate, int hour) {
		if(mmpDayConfs == null) mmpDayConfs = queryMMPConferenceByDay(startdate);
		return findMMPConferenceByHour(mmpDayConfs, hour);
	}

	/**
	 * @param startdate
	 * @return
	 */
	private List<Conference> queryMMPConferenceByDay(String startdate) {
		setDataSource(CustomerContextHolder.MSSQLDATASOURCE);
		String sql = "select billingcode,conferencename,e.date,e.time,e.duration,e.parties,d.customercode,d.customername" +
				" from SummitStorage.dbo.Conference e with (nolock) join SummitStorage.dbo.rl_meetme_Reservation_BillingCode b " +
				"with (nolock) on e.billingcode=b.ReservationBillingCode join NewBOSS.dbo.bo_sv_service c " +
				"with (nolock) on b.meetmeBillingCode=c.servicekey join gnetmis.dbo.bo_cu_customer d  " +
				"with (nolock) on c.customerid=d.customerid where e.valid=0 and " +
				"convert(datetime,e.date)= '"+startdate+"' order by e.time";
		
		logger.debug(sql);
		List<Conference> conferences = this.jdbcTemplate.query(sql, new RowMapper<Conference>() {
			@Override
			public Conference mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Conference c = new Conference();
				c.setBeginTime(rs.getString("date") + rs.getString("time"));
				c.setCustomCode(rs.getString("customercode"));
				c.setCustomName(rs.getString("customername"));
				return c;
			}
		});
		return conferences;
	}

	/**
	 * @param startdate
	 * @param hour 
	 * @return
	 */
	private List<Conference> queryTCConference(String startdate, int hour) {
		if(tcDayConfs == null) tcDayConfs = queryTCConferenceByDay(startdate);
		return findTCConferenceByHour(tcDayConfs, hour);
	}

	/**
	 * @param startdate
	 * @return
	 */
	private List<Conference> queryTCConferenceByDay(String startdate) {
		setDataSource(CustomerContextHolder.TCDATASOURCE);
		String sql = "select t.site_id, t.training_begin_time_system, t.training_name, " +
				"r.customer_code from gnet_tc_training t, gnet_ec_site r where " +
				"t.site_id=r.site_id and t.training_begin_time_system like '"+startdate+"%'";
		return this.jdbcTemplate.query(sql, new RowMapper<Conference>() {
			@Override
			public Conference mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Conference c = new Conference();
				c.setSiteId(rs.getInt("site_id"));
				c.setBeginTime(rs.getString("training_begin_time_system"));
				c.setCustomCode(rs.getString("customer_code"));
				c.setSubject(rs.getString("training_name"));
				return c;
			}
		});
	}

	/**
	 * @param startdate
	 * @param hour 
	 * @return
	 */
	private List<Conference> queryYunConference(String startdate, int hour) {
		if(yunDayConfs == null) yunDayConfs = queryYunConferenceByDay(startdate);
		return findYunConferenceByHour(yunDayConfs, hour);
	}

	/**
	 * @param startdate
	 * @return
	 */
	private List<Conference> queryYunConferenceByDay(String startdate) {
//		setDataSource(CustomerContextHolder.TESTMYSQLDATASOURCE);
//		String sql = "select * from sme_conference where begin_time like '"+startdate+"%'";
		setDataSource(CustomerContextHolder.YUNDATASOURCE);
		String sql = "select c.user_id, c.site_id, subject, DATE_FORMAT(intend_begin_system_time, '%Y-%m-%d %H:%i') begin_time, " +
				"DATE_FORMAT(intend_end_system_time, '%Y-%m-%d %H:%i') end_time, host_user_name, " +
				"host_user_email, host_company_name, s.url, s.customer_code from " +
				"SME.sme_conference c, ums.tang_ums_sites s where intend_begin_system_time like '"+startdate+"%' " +
				"and c.site_id = s.id order by intend_begin_system_time";
		
		return this.jdbcTemplate.query(sql, new RowMapper<Conference>() {
			@Override
			public Conference mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Conference c = new Conference();
				c.setUserId(rs.getLong("user_id"));
				c.setSiteId(rs.getInt("site_id"));
				c.setSubject(rs.getString("subject"));
				c.setBeginTime(rs.getString("begin_time"));
				c.setEndTime(rs.getString("end_time"));
				c.setHostUserName(rs.getString("host_user_name"));
				c.setHostUserEmail(rs.getString("host_user_email"));
				c.setHostCompanyName(rs.getString("host_company_name"));
				c.setUrl(rs.getString("url"));
				c.setCustomCode(rs.getString("customer_code"));
				return c;
			}
		});
	}
	
	/**
	 * @param dataSource
	 */
	private void setDataSource(String dataSource) {
		CustomerContextHolder.setCustomerType(dataSource);
		this.jdbcTemplate = new JdbcTemplate(dynamicDataSource);
	}
	
	@Autowired
	public void setDynamicDataSource(DataSource dynamicDataSource) {
		this.dynamicDataSource = dynamicDataSource;
	}

	/**
	 * 
	 */
	public void clearData() {
		mmpDayConfs = null;
		tcDayConfs = null;
		yunDayConfs = null;
	}
}
